package com.oocl.task0724.dao.impl;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.oocl.task0724.dao.UserDao;
import com.oocl.task0724.pojo.User;
import com.oocl.task0724.util.DBUtil;

public class UserDaoImpl implements UserDao {

	@Override
	public int add(User user) {
		System.out.println( user );
		
		String sql=
				" insert into t_user                     "+
				" (id,username,salary,birth,sex,headImg) "+
				" values                                 "+
				" (user_id_seq.nextval,?,?,?,?,?)";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtil.createConnection();
			pst = con.prepareStatement(sql);

			//idx form 1
			pst.setString(1, user.getUsername() );
			pst.setDouble(2, user.getSalary() );
			pst.setDate(3, new java.sql.Date( user.getBirth().getTime() ));
			pst.setBoolean(4, user.getIsMale());
			pst.setBytes(5, user.getHeadImg());
			
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(null, pst, null);
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	@Override
	public List<User> getAll() {
		List<User> uList=new ArrayList<>();
		String sql=
				" select id,username,salary,birth,sex,headImg " +
				" from t_user" ;
		Connection con=null;
		PreparedStatement pst = null;
		try {
			con=DBUtil.createConnection();
			pst = con.prepareStatement(sql);
			
			ResultSet rs = pst.executeQuery();
			while( rs.next() ){
				User u=new User();
				u.setId( rs.getInt(1) );
				u.setUsername( rs.getString(2) );
				u.setSalary( rs.getDouble(3) );
				u.setBirth( new Date( rs.getDate(4).getTime() ) );
				u.setIsMale( rs.getBoolean(5) );
				u.setHeadImg( rs.getBytes(6) );
				
				uList.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(null, pst, null);
			DBUtil.close(con, pst, null);
		}
		return uList;
	}

	@Override
	public User get(Integer id) {
		User u=null;
		String sql=
				" select id,username,salary,birth,sex,headImg " +
				" from t_user                                 " +
				" where id=?" ;
		Connection con=null;
		PreparedStatement pst = null;
		try {
			con=DBUtil.createConnection();
			pst = con.prepareStatement(sql);

			//idx form 1
			pst.setInt( 1 , id );
			
			ResultSet rs = pst.executeQuery();
			if( rs.next() ){
				u=new User();
				u.setId( rs.getInt(1) );
				u.setUsername( rs.getString(2) );
				u.setSalary( rs.getDouble(3) );
				u.setBirth( new Date( rs.getDate(4).getTime() ) );
				u.setIsMale( rs.getBoolean(5) );
				u.setHeadImg( rs.getBytes(6) );
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(null, pst, null);
			DBUtil.close(con, pst, null);
		}
		return u;
	}

	@Override
	public InputStream getImage(Integer id) {
		InputStream in=null;
		
		String sql=
				" select headImg " +
				" from t_user                                 " +
				" where id=?" ;
		Connection con=null;
		PreparedStatement pst = null;
		try {
			con=DBUtil.createConnection();
			pst = con.prepareStatement(sql);

			//idx form 1
			pst.setInt( 1 , id );
			
			ResultSet rs = pst.executeQuery();
			if( rs.next() ){
				in = rs.getBinaryStream(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(null, pst, null);
			DBUtil.close(con, pst, null);
		}
		return in;
	}

}
